{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# 使用 Alembic 进行数据库迁移\r\n",
    "\r\n",
    "`Alembic` 是 `SQLAlchemy` 的数据库迁移工具。可以将数据库迁移看作是数据库的版本控制。回想一下 SQLAlchemy create_all() 方法只从模型中创建缺少的表。一旦创建了表，它就不会根据模型中的更改改变表模式。\r\n",
    "\r\n",
    "在开发应用程序时，更改表模式是很常见的。这就是阿莱姆比克 `Alembic` 出场的地方。 `Alembic` 之类的工具允许我们随着应用程序的发展更改数据库模式。它还跟踪对数据库所做的更改，以便您可以及时向前或向后移动。如果我们不使用 Alembic 这样的工具，那么我们必须跟踪所有的更改，并通过输入 ALTER 语句手动更改数据库模式。\r\n",
    "\r\n",
    "`Flask-Migrate` 是一个扩展，它将 `Alembic` 与 `Flask` 应用集成在一起。\r\n",
    "\r\n",
    "安装扩展 `pip install flask-migrate`\r\n",
    "\r\n",
    "[官方文档](https://flask-migrate.readthedocs.io/en/latest/)\r\n",
    "\r\n",
    "通过传递应用程序实例(`app`)和 `SQLAlchemy` 对象(`db`) ，从 `flask_Migrate` 包中导入 `Migrate` 和 `MigrateCommand` 类，并创建一个 `Migrate` 类实例，如下所示(突出显示更改) :\r\n",
    "\r\n",
    "flask_app/main2.py\r\n",
    "```python\r\n",
    "#...\r\n",
    "from flask_migrate import Migrate , MigrateCommand \r\n",
    "# flask_migrate 3.0 开始 已不再支持 MigrateCommand `pip install flask_migrate=2.7.0`\r\n",
    "\r\n",
    "app = Flask(__name__)\r\n",
    "app.debug = True\r\n",
    "app.config['SECRET_KEY'] = 'a really really really really long secret key'\r\n",
    "app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:flask123@localhost/flask_app_db'\r\n",
    "app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True # ADD BY YULK  \r\n",
    "\r\n",
    "manager = Manager(app)\r\n",
    "db = SQLAlchemy(app)\r\n",
    "migrate = Migrate(app, db) # 通过传递应用程序实例(`app`)和 `SQLAlchemy` 对象(`db`)\r\n",
    "manager.add_command('db', MigrateCommand) # 第12行\r\n",
    "#...\r\n",
    "```\r\n",
    "MigrateCommand 类定义了一些可以通过 Flask-Script 使用的数据库迁移命令。在第12行中，我们通过 db 命令行参数公开这些命令。要查看新添加的命令，请返回终端并输入以下命令:\r\n",
    "\r\n",
    "`python main2.py`"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "source": [
    "%run main2.py -?"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "usage: main2.py [-?] {db,faker,foo,shell,runserver} ...\n",
      "\n",
      "positional arguments:\n",
      "  {db,faker,foo,shell,runserver}\n",
      "    db                  Perform database migrations\n",
      "    faker               A command to add fake data to the tables\n",
      "    foo                 Just a simple command\n",
      "    shell               Runs a Python shell inside Flask application context.\n",
      "    runserver           Runs the Flask development server i.e. app.run()\n",
      "\n",
      "optional arguments:\n",
      "  -?, --help            show this help message and exit\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "如您所见，我们现在有一个名为 db 的新命令来执行数据库迁移。要查看所有可能的 db 子命令的完整列表，请输入以下命令:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "source": [
    "%run main2.py db -?"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "usage: Perform database migrations\n",
      "\n",
      "Perform database migrations\n",
      "\n",
      "positional arguments:\n",
      "  {init,revision,migrate,edit,merge,upgrade,downgrade,show,history,heads,branches,current,stamp}\n",
      "    init                Creates a new migration repository\n",
      "    revision            Create a new revision file.\n",
      "    migrate             Alias for 'revision --autogenerate'\n",
      "    edit                Edit current revision.\n",
      "    merge               Merge two revisions together. Creates a new migration\n",
      "                        file\n",
      "    upgrade             Upgrade to a later version\n",
      "    downgrade           Revert to a previous version\n",
      "    show                Show the revision denoted by the given symbol.\n",
      "    history             List changeset scripts in chronological order.\n",
      "    heads               Show current available heads in the script directory\n",
      "    branches            Show current branch points\n",
      "    current             Display the current revision for each database.\n",
      "    stamp               'stamp' the revision table with the given revision;\n",
      "                        don't run any migrations\n",
      "\n",
      "optional arguments:\n",
      "  -?, --help            show this help message and exit\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "这些是我们在执行数据库迁移时将要使用的实际命令。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 初始化迁移存储库\r\n",
    "在 Alembic 开始跟踪更改之前，我们必须初始化迁移存储库。迁移存储库只是一个包含 `Alembic` 配置和迁移脚本的`目录`。要创建迁移存储库，执行 init 命令:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "source": [
    "%run main2.py db init"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "Error: Directory migrations already exists and is not empty\n"
     ]
    },
    {
     "output_type": "error",
     "ename": "SystemExit",
     "evalue": "1",
     "traceback": [
      "An exception has occurred, use %tb to see the full traceback.\n",
      "\u001b[1;31mSystemExit\u001b[0m\u001b[1;31m:\u001b[0m 1\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "这个命令将在 flask _ app 目录下创建一个迁移目录。迁移目录的结构应该像这样:\r\n",
    "下面是对每个文件和文件夹的简要介绍:\r\n",
    "```\r\n",
    "migrations\r\n",
    "├── alembic.ini             Alembic 的配置文件\r\n",
    "├── env.py                  每次调用 Alembic 时运行的 Python 文件。它负责连接到数据库，启动事务并调用迁移引擎\r\n",
    "├── README\r\n",
    "├── script.py.mako          Mako 模板文件，用于创建迁移脚本\r\n",
    "└── versions                用于存储迁移脚本的目录\r\n",
    "```\r\n"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 创建迁移脚本 \r\n",
    "\r\n",
    "`Alembic` 将数据库迁移存储在迁移脚本中，而迁移脚本只是 Python 文件。迁移脚本定义了升级upgrade()和降级downgrade()两个函数。升级Upgrade()函数的作用是对数据库应用一组更改，而降级downgrade()函数则逆转这些更改。当我们应用迁移时，它的upgrade()函数会被执行，当我们回滚迁移时，它的降级downgrade()函数会被执行。\r\n",
    "\r\n",
    "Alembic 提供了两种创建迁移的方法:\r\n",
    "\r\n",
    "- 手动通过 `revision`  命令\r\n",
    "- 自动通过 `migrate`  命令"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 人工迁移\r\n",
    "\r\n",
    "手动或空迁移创建一个迁移脚本，其中包含空的 `upgrade()`和 `downgrade()`函数。我们的工作是使用 `Alembic` 指令来填充这些方法，这些指令将对数据库应用一组更改。当我们希望完全控制迁移过程时，可以使用手动迁移。要创建一个空的迁移，输入以下命令:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "source": [
    "%run main2.py db revision -m \"Initial migration\""
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Generating d:\\python_blard\\FLASK\\flask_project\\笔记\\flask_app-16a\\migrations\\versions\\fde35ffcd5d8_initial_migration.py ...  done\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Generating d:\\python_blard\\FLASK\\flask_project\\笔记\\flask_app-16a\\migrations\\versions\\fde35ffcd5d8_initial_migration.py ...  done\r\n",
    "\r\n",
    "这个命令将在 migrations/version 目录中创建一个新的迁移脚本。文件的名称应该是 `someid_initial_migration.py`。打开文件，它应该是这样的:\r\n",
    "\r\n",
    "```python\r\n",
    "\"\"\"'Initial migration'\r\n",
    "\r\n",
    "Revision ID: 1fc9aa1fe77e\r\n",
    "Revises: \r\n",
    "Create Date: 2018-01-23 17:34:19.953371\r\n",
    "\r\n",
    "\"\"\"\r\n",
    "from alembic import op\r\n",
    "import sqlalchemy as sa\r\n",
    "\r\n",
    "\r\n",
    "# revision identifiers, used by Alembic.\r\n",
    "revision = '1fc9aa1fe77e'\r\n",
    "down_revision = None\r\n",
    "branch_labels = None\r\n",
    "depends_on = None\r\n",
    "\r\n",
    "\r\n",
    "def upgrade():\r\n",
    "    pass\r\n",
    "\r\n",
    "\r\n",
    "def downgrade():\r\n",
    "    pass\r\n",
    "```\r\n",
    "\r\n",
    "该文件以一个注释部分开始，其中包含我们使用 `-m` 标志、修订 `ID` 和创建文件的时间戳。下一个重要部分是 修订标识符 `revision id`。每个迁移脚本都有一个修订ID，该修订ID存储在 `revision` 变量中。在下一行中，我们有 `down_revision` 变量，设置为 None。Alembic 使用 down_revision 变量来确定迁移应该以何种顺序运行。Down_revision 变量指向父迁移的 `revision id`。在我们的示例中，它被设置为 `None` ，因为这是我们的第一个迁移脚本。在文件的末尾有空的 `upgrade()` 和 `downgrade()` 函数。\r\n",
    "\r\n",
    "迁移脚本就绪后。让我们编辑迁移文件，将 create table 和 drop table 操作分别添加到 upgrade ()和 degrader ()函数中。\r\n",
    "\r\n",
    "```python\r\n",
    "def upgrade():\r\n",
    "    op.create_table(\r\n",
    "        'users',\r\n",
    "        sa.Column('id', sa.Integer, primary_key=True),\r\n",
    "        sa.Column('name', sa.String(50), nullable=False),\r\n",
    "    )\r\n",
    "\r\n",
    "def downgrade():\r\n",
    "    op.drop_table('users')\r\n",
    "```\r\n",
    "\r\n",
    "在 upgrade() 函数中，我们使用了 Alembic 的 `create_table` 指令。\r\n",
    "\r\n",
    "在 downgrade() 函数中，我们使用了 `drop_table()` 指令，它发出 DROP TABLE 语句。\r\n",
    "\r\n",
    "当您运行这个迁移时，它会创建用户表，当您回滚这个迁移时，它会删除用户表。\r\n",
    "\r\n",
    "我们现在可以应用我们的第一次迁移了:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "source": [
    "%run main2.py db upgrade"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "INFO  [alembic.runtime.migration] Context impl MySQLImpl.\n",
      "INFO  [alembic.runtime.migration] Will assume non-transactional DDL.\n",
      "INFO  [alembic.runtime.migration] Running upgrade  -> 1fc9aa1fe77e, Adding employees table\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "这个命令将调用迁移脚本的 upgrade() 函数。Db 升级命令将数据库迁移到最新的迁移。请注意，db 升级并不只是运行最新的迁移，而是运行所有尚未运行的迁移。这意味着，如果我们创建了大量的迁移，那么 db 升级将会按照创建迁移的顺序运行所有这些迁移。\r\n",
    "\r\n",
    "您还可以传递要运行的迁移的修订 id，而不是运行最新的迁移。在这种情况下，db 升级将在运行指定的迁移之后停止，并且不会继续运行最新的迁移。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "source": [
    "%run main2.py db upgrade 945fc7313080"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "INFO  [alembic.runtime.migration] Context impl MySQLImpl.\n",
      "INFO  [alembic.runtime.migration] Will assume non-transactional DDL.\n"
     ]
    },
    {
     "output_type": "error",
     "ename": "SystemExit",
     "evalue": "1",
     "traceback": [
      "An exception has occurred, use %tb to see the full traceback.\n",
      "\u001b[1;31mSystemExit\u001b[0m\u001b[1;31m:\u001b[0m 1\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "由于这是我们第一次应用迁移，Alembic 还将创建一个名为 `Alembic_version` 的表。该表由一个名为 version _ num 的列组成，该列存储最新应用迁移的修订 id。这就是 Alembic 如何知道当前的移民状态以及移民应该从哪里开始。目前，alembic 版本表看起来像这样:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "source": [
    "# 在Jupyter Notebook中运行SQL\r\n",
    "# 载入sql命令环境\r\n",
    "%load_ext sql\r\n",
    "%sql mysql+pymysql://root:flask123@localhost/flask_app_db"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "%sql  select *  from Alembic_version"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "# posts = %sql  select *  from posts\r\n",
    "# df_posts = posts.DataFrame()\r\n",
    "# df_posts"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "我们可以使用 `db current `确定上一次应用的迁移。它返回上次应用的迁移的修订 id。以防万一，您没有应用任何迁移，它将不返回任何东西。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "source": [
    "%run main2.py db current"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "INFO  [alembic.runtime.migration] Context impl MySQLImpl.\n",
      "INFO  [alembic.runtime.migration] Will assume non-transactional DDL.\n"
     ]
    },
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "1fc9aa1fe77e (head)\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "输出显示我们目前正在移植 `1fc9aa1fe77e`  。另外，注意 revision id 后面的字符串`(head)` ，它表示迁移 `1fc9aa1fe77e` 是最新的迁移。\r\n",
    "\r\n",
    "使用 `db revision` 命令创建另一个空迁移，如下所示:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "source": [
    "%run main2.py db revision -m \"Second migration\""
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Generating d:\\python_blard\\FLASK\\flask_project\\笔记\\flask_app-16a\\migrations\\versions\\17c86cde5407_second_migration.py ...  done\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "再次运行 `db current`  命令。这次您将只获得没有字符串`(head)`的修订 id，因为迁移 `1fc9aa1fe77e` 已经不再是最新的了。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "source": [
    "%run main2.py db current"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "INFO  [alembic.runtime.migration] Context impl MySQLImpl.\n",
      "INFO  [alembic.runtime.migration] Will assume non-transactional DDL.\n"
     ]
    },
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "1fc9aa1fe77e\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "要查看迁移的完整列表(应用的和未应用的) ，请使用 `db history` 命令。它返回一个按反时间顺序排列的迁移列表(即最新的优先)。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "source": [
    "%run main2.py db history"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "1fc9aa1fe77e -> 17c86cde5407 (head), 'Second migration'\n",
      "<base> -> 1fc9aa1fe77e, Adding employees table\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "输出显示 <base> -> 1fc9aa1fe77e 是我们的第一次迁移，\r\n",
    "然后是最新的迁移 1fc9aa1fe77e -> 17c86cde5407 。像往常一样 `(head)` 表示最近的迁移。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "我们通过迁移创建的用户 `employees` 表纯粹是为了测试目的。通过降低迁移级别，我们可以使数据库恢复到它在执行 `db upgrade` 命令之前的原始状态。要降级或回滚上次应用的迁移，我们使用 `db downgrade` 命令。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "source": [
    "%run  main2.py db downgrade"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "INFO  [alembic.runtime.migration] Context impl MySQLImpl.\n",
      "INFO  [alembic.runtime.migration] Will assume non-transactional DDL.\n",
      "INFO  [alembic.runtime.migration] Running downgrade 1fc9aa1fe77e -> , Adding employees table\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "这将调用 `downgrade()` 迁移方法 `1fc9aa1fe77e` ，该方法从数据库中删除用户 `employees`   表。与 `db upgrade` 命令一样，我们也可以传递要降级到的迁移的 revision id。例如，要降级到迁移645fc5113912，我们可以使用以下命令。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "source": [
    "%run main2.py db downgrade 1fc9aa1fe77e"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "INFO  [alembic.runtime.migration] Context impl MySQLImpl.\n",
      "INFO  [alembic.runtime.migration] Will assume non-transactional DDL.\n"
     ]
    },
    {
     "output_type": "error",
     "ename": "SystemExit",
     "evalue": "1",
     "traceback": [
      "An exception has occurred, use %tb to see the full traceback.\n",
      "\u001b[1;31mSystemExit\u001b[0m\u001b[1;31m:\u001b[0m 1\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "要回滚所有应用的迁移，请使用以下命令:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "source": [
    "%run main2.py db downgrade base"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "INFO  [alembic.runtime.migration] Context impl MySQLImpl.\n",
      "INFO  [alembic.runtime.migration] Will assume non-transactional DDL.\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "此时，我们还没有应用到数据库的迁移。我们可以通过运行如下的 db current 命令来验证这一点:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "source": [
    "%run main2.py db current"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "INFO  [alembic.runtime.migration] Context impl MySQLImpl.\n",
      "INFO  [alembic.runtime.migration] Will assume non-transactional DDL.\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "正如您所看到的，输出返回没有 revision id。请注意，降级迁移只会撤销对数据库所做的更改，而不会删除迁移脚本本身。因此，我们仍然有两个迁移脚本，以查看它们运行 db 历史命令。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "source": [
    "%run main2.py db history"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "1fc9aa1fe77e -> 17c86cde5407 (head), 'Second migration'\n",
      "<base> -> 1fc9aa1fe77e, Adding employees table\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "那么，如果我们现在运行 db 升级命令会发生什么呢？\r\n",
    "\r\n",
    "`db upgrade` 命令首先运行 `migration 1fc9aa1fe77e` 然后是 `migration 17c86cde5407 `\r\n",
    "\r\n",
    "如果你是这么想的话。干得好！现在您应该对迁移有了相当好的理解。我们的数据库再次处于完美状态，我们不希望在迁移脚本中应用更改，以便可以安全地删除它们。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 自动迁移\r\n",
    "\r\n",
    "注意: 在继续之前，请确保删除了前面部分中的所有迁移。\r\n",
    "\r\n",
    "自动迁移在将模型与数据库的当前版本进行比较之后，为 `upgrade()` 和 `downgrade()` 函数创建代码。为了创建自动迁移，我们使用 `migrate` 命令，它只是 `revision --autogenerate` 的别名。在终端输入 `migrate` 命令如下:\r\n",
    "\r\n"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "source": [
    "%run main2.py db init\r\n",
    "%run main2.py db migrate"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "INFO  [alembic.runtime.migration] Context impl MySQLImpl.\n",
      "INFO  [alembic.runtime.migration] Will assume non-transactional DDL.\n"
     ]
    },
    {
     "output_type": "error",
     "ename": "SystemExit",
     "evalue": "1",
     "traceback": [
      "An exception has occurred, use %tb to see the full traceback.\n",
      "\u001b[1;31mSystemExit\u001b[0m\u001b[1;31m:\u001b[0m 1\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "注意输出的最后一行，它说`\"No changes in schema detected.\"`没有检测到模式的更改, 这意味着我们的模型和数据库是同步的。\r\n",
    "\r\n",
    "打开 main2.py，在 Feedback 模型之后添加 Employee 模型，如下所示:\r\n",
    "\r\n",
    "flask_app/main2.py\r\n",
    "```python\r\n",
    "#...\r\n",
    "class Employee(db.Model):\r\n",
    "    __tablename__ = 'employees'\r\n",
    "    id = db.Column(db.Integer(), primary_key=True)\r\n",
    "    name = db.Column(db.String(255), nullable=False)\r\n",
    "    designation = db.Column(db.String(255), nullable=False)\r\n",
    "    doj = db.Column(db.Date(), nullable=False)    \r\n",
    "#...\r\n",
    "```\r\n",
    "再次运行 db migrate 命令，这次 Alembic 将检测新增的 employees 表，并生成一个迁移脚本以及创建和删除 employees 表的逻辑。\r\n"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "%run main2.py db migrate -m \"Adding employees table\""
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "前面的命令创建的迁移脚本应该是这样的:\r\n",
    "\r\n",
    "```python\r\n",
    "\r\n",
    "\"\"\"Adding employees table\r\n",
    "\r\n",
    "Revision ID: 9bc3608c7752\r\n",
    "Revises: \r\n",
    "Create Date: 2021-08-30 10:58:42.388366\r\n",
    "\r\n",
    "\"\"\"\r\n",
    "from alembic import op\r\n",
    "import sqlalchemy as sa\r\n",
    "\r\n",
    "\r\n",
    "# revision identifiers, used by Alembic.\r\n",
    "revision = '9bc3608c7752'\r\n",
    "down_revision = None\r\n",
    "branch_labels = None\r\n",
    "depends_on = None\r\n",
    "\r\n",
    "\r\n",
    "def upgrade():\r\n",
    "    # ### commands auto generated by Alembic - please adjust! ###\r\n",
    "    op.create_table('employees',\r\n",
    "    sa.Column('id', sa.Integer(), nullable=False),\r\n",
    "    sa.Column('name', sa.String(length=255), nullable=False),\r\n",
    "    sa.Column('designation', sa.String(length=255), nullable=False),\r\n",
    "    sa.Column('doj', sa.Date(), nullable=False),\r\n",
    "    sa.PrimaryKeyConstraint('id')\r\n",
    "    )\r\n",
    "    # ### end Alembic commands ###\r\n",
    "\r\n",
    "\r\n",
    "def downgrade():\r\n",
    "    # ### commands auto generated by Alembic - please adjust! ###\r\n",
    "    op.drop_table('employees')\r\n",
    "    # ### end Alembic commands ###\r\n",
    "\r\n",
    "```\r\n",
    "\r\n",
    "这里没有什么新的东西，upgrade ()函数使用 create_table 指令创建表，而 downgrade()函数使用 drop_table  指令删除表。\r\n",
    "\r\n",
    "让我们使用 db upgrade 命令运行这次迁移:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "source": [
    "%run main2.py db upgrade"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "INFO  [alembic.runtime.migration] Context impl MySQLImpl.\n",
      "INFO  [alembic.runtime.migration] Will assume non-transactional DDL.\n",
      "INFO  [alembic.runtime.migration] Running upgrade  -> 9bc3608c7752, Adding employees table\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "这将在数据库中添加雇员表。我们可以通过查看数据库来验证更改，如下所示:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "source": [
    "# %sql desc employees \r\n",
    "%sql show create table employees"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      " * mysql+pymysql://root:***@localhost/flask_app_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('employees', 'CREATE TABLE `employees` (\\n  `id` int NOT NULL AUTO_INCREMENT,\\n  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,\\n  `designation` varchar( ... (6 characters truncated) ... OLLATE utf8mb4_unicode_ci NOT NULL,\\n  `doj` date NOT NULL,\\n  PRIMARY KEY (`id`)\\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci')]"
      ],
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Table</th>\n",
       "        <th>Create Table</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>employees</td>\n",
       "        <td>CREATE TABLE `employees` (<br>  `id` int NOT NULL AUTO_INCREMENT,<br>  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,<br>  `designation` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,<br>  `doj` date NOT NULL,<br>  PRIMARY KEY (`id`)<br>) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci</td>\n",
       "    </tr>\n",
       "</table>"
      ]
     },
     "metadata": {},
     "execution_count": 38
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 自动迁移的局限性\r\n",
    "\r\n",
    "自动迁移并不完美，它不能检测到每一个可能的变化。\r\n",
    "\r\n",
    "Alembic 能够检测到的操作:\r\n",
    "\r\n",
    "- 增加或移除表格\r\n",
    "- 增加或移除列名\r\n",
    "- 外键的更改\r\n",
    "- 列类型的更改\r\n",
    "- 索引中的更改\r\n",
    "- 显式命名的唯一约束(unique constraints)\r\n",
    "\r\n",
    "Alembic 无法检测的操作:\r\n",
    "\r\n",
    "- 更改表名\r\n",
    "- 列名更改\r\n",
    "- 没有显式名称的约束\r\n",
    "\r\n",
    "为了创建 Alembic 无法检测的操作的迁移脚本，我们必须创建一个空的迁移脚本，然后相应地填充 `upgrade`() 和 `downgrade`()函数。"
   ],
   "metadata": {}
  }
 ],
 "metadata": {
  "orig_nbformat": 4,
  "language_info": {
   "name": "python",
   "version": "3.9.6",
   "mimetype": "text/x-python",
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "pygments_lexer": "ipython3",
   "nbconvert_exporter": "python",
   "file_extension": ".py"
  },
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3.9.6 64-bit"
  },
  "interpreter": {
   "hash": "c644d696b95f5e0f4df3c6556741cf30bcf9ea6ca93c3e1f29fcf31d885534fc"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}